package com.qiniu.githubdata.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.data.repository.query.Param;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.qiniu.githubdata.entity.Developer;
import com.qiniu.githubdata.entity.DeveloperFollower;
import com.qiniu.githubdata.entity.dto.NationCount;
// import org.springframework.web.bind.annotation.RequestParam;

@Mapper
public interface DeveloperMapper extends BaseMapper<Developer> {
//    void save(Developer developer);
@Select("SELECT id FROM developer WHERE login LIKE CONCAT('%', #{name}, '%') OR user_name LIKE CONCAT('%', #{name}, '%')")
List<Long> searchByName(String name);


@Insert("INSERT INTO developer_follower (developer_id, follower_id) VALUES (#{developerId}, #{followerId}) " +
        "ON DUPLICATE KEY UPDATE developer_id = #{developerId}, follower_id = #{followerId}")
void insertFollowerIfNotExist( DeveloperFollower developerFollower);
    
    @Select("SELECT d.nation, COUNT(*) as count " +
            "FROM developer_follower df " +
            "JOIN developer d ON df.follower_id = d.id " +
            "WHERE df.developer_id = #{developerId} " +
            "AND d.nation IS NOT NULL " +
            "AND d.nation_confidence > 50 " +
            "GROUP BY d.nation " +
            "ORDER BY count DESC")
    List<NationCount> getFollowersNationCount(@Param("developerId") Long developerId);

    //insert or update developer
    @Insert("INSERT INTO developer (id, login, user_name, nation, nation_confidence, evaluation_info) " +
            "VALUES (#{id}, #{login}, #{userName}, #{nation}, #{nationConfidence}, #{evaluationInfo}) " +
            "ON DUPLICATE KEY UPDATE " +
            "nation = #{nation}, nation_confidence = #{nationConfidence}, evaluation_info = #{evaluationInfo}")
    void insertOrUpdateDeveloper(Developer developer);
    @Select("WITH RECURSIVE follower_chain AS (" +
        "  SELECT df1.developer_id, df1.follower_id, 1 as level " +
        "  FROM developer_follower df1 " +
        "  WHERE df1.developer_id = #{developerId} " +
        "  UNION " +
        "  SELECT df2.developer_id, df2.follower_id, fc.level + 1 " +
        "  FROM developer_follower df2 " +
        "  JOIN follower_chain fc ON df2.developer_id = fc.follower_id " +
        "  WHERE fc.level < 2" +
        ") " +
        "SELECT d.nation, COUNT(*) as count " +
        "FROM follower_chain fc " +
        "JOIN developer d ON fc.follower_id = d.id " +
        "WHERE fc.level = 2 " +
        "AND d.nation IS NOT NULL " +
        "AND d.nation_confidence > 50 " +
        "GROUP BY d.nation " +
        "ORDER BY count DESC")
List<NationCount> getSecondLevelFollowersNationCount(@Param("developerId") Long developerId);

}
